package com.duanjc.dbtool.service.impl;

import cn.hutool.core.exceptions.UtilException;
import cn.hutool.core.io.FileUtil;
import com.duanjc.dbtool.config.DataBaseInfo;
import com.duanjc.dbtool.service.MySqlDBService;
import com.duanjc.dbtool.utils.FileTool;
import com.duanjc.dbtool.utils.MysqlUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.DbUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Service;
import org.zeroturnaround.zip.ZipUtil;


import javax.sql.DataSource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * The type Db service.
 */
@Slf4j
@Service
public class MySqlDBServiceImpl implements MySqlDBService {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private DataBaseInfo dataBaseInfo;

    /**
     * Export.
     */
    @Override
    public void export(){
        //connect to the database
        String dbName = findDBName(dataBaseInfo.getUrl());
        //generate the final SQL
        String sql = exportToSql();

        //create a temp dir to store the exported file for processing
        String dirName = dataBaseInfo.getBackUpPath();
        File file = new File(dirName);
        if (!file.exists()) {
            boolean res = file.mkdir();
            if (!res) {
                log.error("Unable to create temp dir: " + file.getAbsolutePath(),
                        new IOException("Unable to create temp dir: " + file.getAbsolutePath()));
            }
        }

        //write the sql file out
        File sqlFolder = new File(dirName + "/sql");
        if (!sqlFolder.exists()) {
            boolean res = sqlFolder.mkdir();
            if (!res) {
                log.error("Unable to create temp sql dir: " + file.getAbsolutePath(),
                        new IOException("Unable to create temp sql dir: " + file.getAbsolutePath()));
            }
        }

        String sqlFileName = getSqlFilename(dbName);
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(sqlFolder + "/" + sqlFileName);
            outputStream.write(sql.getBytes());

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        //zip the file
        String zipFileName = dirName + "/" + sqlFileName.replace(".sql", ".zip");
        File generatedZipFile = new File(zipFileName);
        ZipUtil.pack(sqlFolder, generatedZipFile);

        //clear the generated temp files
        clearTempFiles(true, sqlFileName, zipFileName);
    }

    /**
     * Export ddl.
     */
    @Override
    public void exportDDL() {

    }

    /**
     * Export dml.
     */
    @Override
    public void exportDML() {

    }

    /**
     * Recover.
     */
    @Override
    public void recover() {
        Connection connection = null;
        try {
            //get backup file
            File[] files = FileUtil.ls(dataBaseInfo.getBackUpPath());
            //get the latest file
            List<File> fileList = Arrays.stream(files).sorted((o1, o2) -> {
                if (FileTool.getFileCreateTime(o1).compareTo(FileTool.getFileCreateTime(o2)) > 0) {
                    return -1;
                }
                return 1;
            }).collect(Collectors.toList());

            //unzip sql file
            File unzipPath = cn.hutool.core.util.ZipUtil.unzip(fileList.get(0));
            File[] sqlFiles = FileUtil.ls(unzipPath.getAbsolutePath());

            connection = dataSource.getConnection();
            for(File f : sqlFiles){
                Resource resource = new FileSystemResource(f);
                EncodedResource resourceDelegate = new EncodedResource(resource,"utf-8");
                ScriptUtils.executeSqlScript(connection, resourceDelegate);
            }

            FileUtil.del(unzipPath);
        } catch (UtilException | SQLException e) {
            log.error("========Data recovery exception", e);
        } finally {
            DataSourceUtils.releaseConnection(connection, dataSource);
        }
    }


    /**
     * 生成create语句
     *
     * @param table the table concerned
     * @return String
     * @throws SQLException exception
     */
    private String getTableInsertStatement(String table, Statement stmt) throws SQLException {

        StringBuilder sql = new StringBuilder();
        ResultSet rs = null;
        boolean addIfNotExists = true;


        try {
            if (table != null && !table.isEmpty()) {
                rs = stmt.executeQuery("SHOW CREATE TABLE " + "`" + table + "`;");
                while (rs.next()) {
                    String qtbl = rs.getString(1);
                    String query = rs.getString(2);
                    sql.append("\n\n--");
                    sql.append("\n").append(MysqlUtil.SQL_START_PATTERN).append("  table dump : ").append(qtbl);
                    sql.append("\n--\n\n");

                    if (addIfNotExists) {
                        query = query.trim().replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
                    }

                    sql.append(query).append(";\n\n");
                }

                sql.append("\n\n--");
                sql.append("\n").append(MysqlUtil.SQL_END_PATTERN).append("  table dump : ").append(table);
                sql.append("\n--\n\n");
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            try {
                DbUtils.close(rs);
            } catch (SQLException e) {

            }
        }

        return sql.toString();
    }


    /**
     * 生成insert语句
     *
     * @param table the table to get inserts statement for
     * @return String generated SQL insert
     * @throws SQLException exception
     */
    private String getDataInsertStatement(String table, Statement stmt) throws SQLException {

        StringBuilder sql = new StringBuilder();
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery("SELECT * FROM " + "`" + table + "`;");
            rs.last();
            int rowCount = rs.getRow();
            if (rowCount <= 0) {
                return sql.toString();
            }

            sql.append("\n--").append("\n-- Inserts of ").append(table).append("\n--\n\n");
            sql.append("\n/*!40000 ALTER TABLE `").append(table).append("` DISABLE KEYS */;\n");
            sql.append("\n--\n")
                    .append(MysqlUtil.SQL_START_PATTERN).append(" table insert : ").append(table)
                    .append("\n--\n");
            sql.append("INSERT INTO `").append(table).append("`(");

            ResultSetMetaData metaData = rs.getMetaData();

            int columnCount = metaData.getColumnCount();

            for (int i = 0; i < columnCount; i++) {
                sql.append("`")
                        .append(metaData.getColumnName(i + 1))
                        .append("`, ");
            }

            sql.deleteCharAt(sql.length() - 1).deleteCharAt(sql.length() - 1).append(") VALUES \n");

            rs.beforeFirst();
            while (rs.next()) {
                sql.append("(");
                for (int i = 0; i < columnCount; i++) {

                    int columnType = metaData.getColumnType(i + 1);
                    int columnIndex = i + 1;

                    if (Objects.isNull(rs.getObject(columnIndex))) {
                        sql.append("").append(rs.getObject(columnIndex)).append(", ");
                    } else if (columnType == Types.INTEGER || columnType == Types.TINYINT || columnType == Types.BIT) {
                        sql.append(rs.getInt(columnIndex)).append(", ");
                    } else {

                        String val = rs.getString(columnIndex);
                        val = val.replace("'", "\\'");

                        sql.append("'").append(val).append("', ");
                    }
                }


                sql.deleteCharAt(sql.length() - 1).deleteCharAt(sql.length() - 1);

                if (rs.isLast()) {
                    sql.append(")");
                } else {
                    sql.append("),\n");
                }
            }

            sql.append(";");

            sql.append("\n--\n")
                    .append(MysqlUtil.SQL_END_PATTERN).append(" table insert : ").append(table)
                    .append("\n--\n");

            //enable FK constraint
            sql.append("\n/*!40000 ALTER TABLE `").append(table).append("` ENABLE KEYS */;\n");
        } catch (SQLException e) {
            throw e;
        } finally {
            DbUtils.close(rs);
        }

        return sql.toString();
    }

    /**
     * 导出所有表的结构和数据
     *
     * @return String
     * @throws SQLException exception
     */
    private String exportToSql() {

        StringBuilder sql = new StringBuilder();
        sql.append("--");
        sql.append("\n-- Generated by workbgm");
        sql.append("\n-- Date: ").append(new SimpleDateFormat("d-M-Y H:m:s").format(new Date()));
        sql.append("\n--");

        //these declarations are extracted from HeidiSQL
        sql.append("\n\n/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;")
                .append("\n/*!40101 SET NAMES utf8 */;")
                .append("\n/*!50503 SET NAMES utf8mb4 */;")
                .append("\n/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;")
                .append("\n/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;");
        //all tables
        List<String> tables = null;

        String dbName = findDBName(dataBaseInfo.getUrl());
        //String dbName = findDBName(dataSource);
        Connection connection = null;
        Statement stmt = null;
        try {
            connection = dataSource.getConnection();
            stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            tables = MysqlUtil.getAllTables(dbName, stmt);
            for (String s : tables) {
                sql.append(getTableInsertStatement(s.trim(), stmt));
                /*skip tables*/
                if (dataBaseInfo.getSkipTables().contains(s)) {
                    continue;
                }
                sql.append(getDataInsertStatement(s.trim(), stmt));
            }
        } catch (SQLException e) {
            log.error("Generate backup database SQL statement exception!", e);
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                log.error("close database connection exception!", e);
            }
            try {
                DbUtils.close(stmt);
            } catch (SQLException e) {
                log.error("close database Statement exception!", e);
            }
        }


        sql.append("\n/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;")
                .append("\n/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;")
                .append("\n/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;");

        return sql.toString();
    }

    /**
     * 从jdbcUrl中提取数据库名
     *
     * @param jdbcUrl
     * @return
     */
    public String findDBName(String jdbcUrl) {
        String dbName = "";
        int s = jdbcUrl.lastIndexOf("/") + 1;
        int e = jdbcUrl.lastIndexOf("?");
        dbName = jdbcUrl.substring(s, e);
        return dbName;
    }

    /**
     * 清理临时文件
     *
     * @param preserveZipFile bool
     */
    public void clearTempFiles(boolean preserveZipFile, String sqlFileName,String zipFileName) {

        String dirName = dataBaseInfo.getBackUpPath();

        //delete the temp sql file
        File sqlFile = new File(dirName + "/sql/" + sqlFileName);
        if (sqlFile.exists()) {
            boolean res = sqlFile.delete();
            System.out.println(": " + sqlFile.getAbsolutePath() + " deleted successfully? " + (res ? " TRUE " : " FALSE "));
        } else {
            System.out.println(": " + sqlFile.getAbsolutePath() + " DOES NOT EXIST while clearing Temp Files");
        }

        File sqlFolder = new File(dirName + "/sql");
        if (sqlFolder.exists()) {
            boolean res = sqlFolder.delete();
            System.out.println(": " + sqlFolder.getAbsolutePath() + " deleted successfully? " + (res ? " TRUE " : " FALSE "));
        } else {
            System.out.println(": " + sqlFolder.getAbsolutePath() + " DOES NOT EXIST while clearing Temp Files");
        }


        if (!preserveZipFile) {

            //delete the zipFile
            File zipFile = new File(zipFileName);
            if (zipFile.exists()) {
                boolean res = zipFile.delete();
                System.out.println(": " + zipFile.getAbsolutePath() + " deleted successfully? " + (res ? " TRUE " : " FALSE "));
            } else {
                System.out.println(": " + zipFile.getAbsolutePath() + " DOES NOT EXIST while clearing Temp Files");
            }

            //delete the temp folder
            File folder = new File(dirName);
            if (folder.exists()) {
                boolean res = folder.delete();
                System.out.println(": " + folder.getAbsolutePath() + " deleted successfully? " + (res ? " TRUE " : " FALSE "));
            } else {
                System.out.println(": " + folder.getAbsolutePath() + " DOES NOT EXIST while clearing Temp Files");
            }
        }

        System.out.println(": generated temp files cleared successfully");
    }

    /**
     * 生成文件名
     * sql file name.
     *
     * @return String
     */
    public String getSqlFilename(String dbName) {
        return new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss").format(new Date()) + "_" + dbName + "_database_dump.sql";
    }


    /**
     * 获取备份的sql zip文件
     *
     * @return
     */
    public File getGeneratedZipFile() {
        /*if (generatedZipFile != null && generatedZipFile.exists()) {
            return generatedZipFile;
        }*/
        return null;
    }
}
